package com.decent.ejfadmin.report.dao.provider;

import com.decent.ejfadmin.common.bean.Page;
import com.decent.ejfadmin.utils.SQLWrapUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author weicx
 * @date 2018/08/01
 */
public class CollectCardStockReportDaoProvider {
    /**
     * 收卡卡密库存统计计数
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardStockReportCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select count(t.listCount) from (select count(1) as listCount ");
        sql.append(" from yuhuang_supply_card sc where sc.startTime >= date_sub(now(),interval 1 month)" +
                " AND sc.status IN (0, 3, 7, 8) ");
        generateStockSql(sql, params);
        sql.append(" group by sc.productType,sc.face) t");
        return sql.toString();
    }

    /**
     * 收卡卡密库存统计
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardStockReportPage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select sc.productType,sc.face,count(1) as totalStockAmount,cp.card_operator_name,sum(sc.face) as totalUploadFace ");
        sql.append(" from yuhuang_supply_card sc ,yuhuang_card_operator cp\n" +
                "WHERE sc.productType = cp.card_operator_id and sc.startTime >= date_sub(now(),interval 1 month)" +
                " AND sc.status IN (0, 3, 7, 8) ");
        generateStockSql(sql, params);
        sql.append(" group by sc.productType,sc.face order by sc.productType,sc.face ");
        Page page = (Page) params.get("pageUnit");
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 分页查询收卡卡密库存统计组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateStockSql(StringBuffer sql, HashMap<String, Object> params) {
        //运营商
        if (params.get("productType") != null) {
            sql.append(" and sc.productType = #{productType} ");
        }
        //面值
        if (StringUtils.isNotBlank((String) params.get("face"))) {
            sql.append(" and sc.face = #{face} ");
        }
    }

    /**
     * 分页查询收卡库存折扣计数
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardStockPriceCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT count(1) FROM (SELECT t1.area FROM (SELECT ");
        generateStockPriceSql(sql, params);
        sql.append(" GROUP BY t1.userPrice,t1.area) t2");
        return sql.toString();
    }

    /**
     * 折扣详情组织sql
     *
     * @param sql sql
     */
    private void generateStockPriceSql(StringBuffer sql, HashMap<String, Object> params) {
        sql.append(" sc.userPrice,if(sc.supplyArea = 0, '全国', sc.area) area from yuhuang_supply_card sc where sc.startTime >= " +
                "date_sub(now(),interval 1 month) and sc.productType = #{productType} and " +
                "sc.face = #{face} AND sc.status IN (0, 3, 7, 8) GROUP BY sc.productType, sc.face, " +
                "sc.userPrice, sc.supplyArea, sc.area) t1 ");
    }

    /**
     * 分页查询收卡库存折扣
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardStockPricePage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT sum(t1.amount) AS totalStockAmount,t1.userPrice,t1.area FROM (SELECT count(1) AS amount,");
        generateStockPriceSql(sql, params);
        sql.append(" GROUP BY t1.userPrice, t1.area ORDER BY t1.area, t1.userPrice ");
        Page page = (Page) params.get("pageUnit");
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 查询会员库存中待销卡的最低折扣
     *
     * @param params 参数
     * @return
     */
    public String getMinUserPrice(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT\n" +
                "\t\tmin(userPrice) minUserPrice\n" +
                "\tFROM\n" +
                "\t\tyuhuang_supply_card sc,\n" +
                "\t\tyuhuang_sup_user u\n" +
                "\tWHERE\n" +
                "\t\tsc.userId = u.user_id\n" +
                "\tAND u.account = #{account}\n" +
                "\tAND sc. STATUS IN (0, 3, 7, 8)\n" +
                "\tAND productType = #{productType}\n" +
                "\tAND face = #{face}");
        if ("全国".equals(params.get("area"))) {
            sql.append(" and sc.supplyArea = 0");
        } else {
            sql.append(" and sc.supplyArea = 1 and sc.area = #{area}");
        }
        return sql.toString();
    }

    /**
     * 查询库存中小于该折扣的库存数量
     *
     * @param params 参数
     * @return
     */
    public String getFrontCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT\n" +
                "\tCOUNT(1)\n" +
                "FROM\n" +
                "\tyuhuang_supply_card sc\n" +
                "\tWHERE\n" +
                "\t sc. STATUS IN (0, 3, 7, 8)\n" +
                "\tAND sc.userPrice < #{minUserPrice}\n" +
                "\tAND productType = #{productType}\n" +
                "\tAND face = #{face}");
        if ("全国".equals(params.get("area"))) {
            sql.append(" and sc.supplyArea = 0");
        } else {
            sql.append(" and sc.supplyArea = 1 and sc.area = #{area}");
        }
        return sql.toString();
    }

}
